<?php

namespace platform\models;

use common\services\Authorization;
use platform\core\BaseModel;
use cm;
use Yii;
use yii\helpers\Url;
use common\models\CmDataModel;

class SysModel extends BaseModel
{
    //分销状态1开 0关
    const REBATE_YES=1;
    const REBATE_NOE=0;
    //分销状态1比率 2金额
    const REBATE_TYPE_RATIO=1;
    const REBATE_TYPE_PRICE=2;

    /*
    * 权限管理
    */
    public function actionAuth()
    {
        if (Cm::$req->isPost()) {
            $action = Cm::$req->get('action');
            switch ($action) {
                case "refresh":
                    if(empty($this->sysid)){
                        Cm::$res->error('错误操作');
                    }
                    (new Authorization())->refresh($this->sysid);
                    Cm::$app->log->authlog(8,"",$this->sysid);
                    break;
            }
        }

        $data['map'] = [];
        $data['map'][] = ['title' => "权限管理", 'url' => Url::to(['sys/auth'])];
        return $data;
    }

    /*
    * 角色管理
    */
    public function actionRole()
    {
        if (Yii::$app->request->method == 'POST') {
            $action = Cm::$req->get('action');
            switch ($action) {
                case 'list':
                    $pageIndex = Cm::$req->post('page', ['null', 'empty', 'default' => 1]);
                    $pageCount = Cm::$req->post('limit', ['null', 'empty', 'default' => 20]);
                    $data = Cm::$db->paging("SELECT * FROM sys_auth_role WHERE sys_id={$this->sysid} AND status !=2 ORDER BY sort ASC,status ASC",
                        $pageIndex, $pageCount);
                    Cm::$res->paging($data);
                    break;
                case 'info':
                    $id = Cm::$req->post('id', ['null', 'empty', 'default' => 0]);
                    $info = Cm::$db->one("SELECT * FROM sys_auth_role WHERE id={$id}");
                    if (!$info) {
                        $info = ['id' => 0, 'parent_id' => 0, 'name' => '', 'remark' => '', 'status' => 1, 'is_superman' => 0];
                    }
                    $data['info'] = $info;
                    Cm::$res->susess($data);
                    break;
                case 'get_permission':
                    $id = Cm::$req->post('id', ['null', 'empty', 'default' => 0]);
                    $data = [];
                    $data['permissions'] = $this->getRolePermissions($this->sysid, $id);
                    Cm::$app->log->authlog(7,$data,$id);
                    Cm::$res->susess($data);    
                    break;    
                case 'save_permission':
                    $roleID = Cm::$req->post('id', ['null', 'empty']);
                    if(empty($roleID)){
                        Cm::$res->error('错误操作');
                    }
                    $permissionsIds = Cm::$req->post('permissions_ids', ['null', 'empty', 'default' => '']);

                    $actionPerArr = explode(',', $permissionsIds);
                    Cm::$db->delete("sys_auth_role_permissions", "role_id={$roleID}");
                    if(!empty($actionPerArr)){
                        $actionPermission = [];
                        foreach ($actionPerArr as $id) {
                            if ($id)
                                $actionPermission[] = ["id" => $id];
                        }

                        foreach ($actionPermission as $p) {
                            Cm::$db->insert("sys_auth_role_permissions", ["role_id" => $roleID, "permissions_id" => $p['id']]);
                        }
                        Cm::$app->log->authlog(6,$actionPerArr,$roleID);
                    }    
                    Cm::$res->susess([], '变更成功');
                    break;    
                case 'save':
                    $id = Cm::$req->post('id', ['null', 'empty', 'default' => 0]);
                    $mdata = [
                        'sys_id' => $this->sysid,
                        'name' => Cm::$req->post('name',['title'=>'角色名称']),
                        'remark' => Cm::$req->post('remark', ['null', 'empty']),
                        'is_superman' => (int)Cm::$req->post('is_superman', ['null', 'empty']) ? 1 : 0,
                        'status' => Cm::$req->post('status',['null','empty','default'=>1]),
                        'sort' => Cm::$req->post('sort',['null','empty','default'=>0]), 
                        'updatetime' => date('Y-m-d H:i:s')
                    ];
                    if (!empty($id)) {
                        Cm::$db->update('sys_auth_role', $mdata, "id={$id}");
                        Cm::$app->log->authlog(9,$mdata,$id);
                    } else {
                        $mdata['addtime'] = date('Y-m-d H:i:s');
                        $id = Cm::$db->insert('sys_auth_role', $mdata);
                        if(!$id){
                            Cm::$res->error('保存失败');
                        }
                        Cm::$app->log->authlog(10,$mdata,$id);
                    }
                    Cm::$res->susess([], '保存成功');
                    break;
                case 'del':
                    $id = Cm::$req->post('id', ['null', 'empty']);
                    $is_superman = Cm::$req->post('is_superman', ['null', 'empty','default'=>0]);
                    if($is_superman == 1){
                        Cm::$res->error('超级角色无法删除！');
                    }
                    if(empty($id)){
                        Cm::$res->error('错误操作');
                    }
                    Cm::$db->update("sys_auth_role", ['status' => 2], "id={$id}");
                    Cm::$app->log->authlog(11,['status' => 2],$id);
                    Cm::$res->susess([], '删除成功');
                    break;
            }
        }

        $data['map'] = [];
        $data['map'][] = ['title' => "权限管理", 'url' => Url::to(['sys/auth'])];
        $data['map'][] = ['title' => "角色管理", 'url' => Url::to(['sys/role'])];
        $data['baseUrl'] = Url::to(['sys/role']);
        return $data;
    }

    /*
    * 管理员管理
    */
    public function actionManager()
    {
        if (Yii::$app->request->method == 'POST') {
            $action = Cm::$req->get('action');
            switch ($action) {
                case "list":
                    $pageIndex = Cm::$req->post('page', ['null', 'empty', 'default' => 1]);
                    $pageCount = Cm::$req->post('limit', ['null', 'empty', 'default' => 20]);
                    $key = Cm::$req->post('key', ['null', 'empty', 'default' => '']);
                    $status = (int)Cm::$req->post('is_status', ['null', 'empty']);
                    $is_check = (int)Cm::$req->post('is_check', ['null', 'empty']);
                    $where = "";
                    if(!empty($is_check)){
                        $is_check = $is_check == 2 ? 0 : $is_check;
                        $where .= " AND a.is_check={$is_check} ";
                    }
                    if(!empty($status)){
                        $status = $status == 2 ? 0 : $status;
                        $where .= " AND a.status={$status} ";
                    }else{
                        $where .= " AND a.status!=2 ";
                    }
                    if(!empty($key)){
                        $where .= " AND a.username LIKE '%{$key}%' ";
                    }
                    $data = Cm::$db->paging("SELECT a.*,b.role_id,c.name AS role_name
                            FROM sys_manager a 
                            LEFT JOIN sys_manager_role b ON a.id=b.manager_id 
                            LEFT JOIN sys_auth_role c ON b.role_id=c.id
                            WHERE a.sys_id={$this->sysid} AND a.type=1 {$where} ORDER BY a.addtime DESC",
                        $pageIndex, $pageCount);
                    Cm::$res->paging($data);
                    break;
                case "save":
                    $id = Cm::$req->post('id');
                    $roleID = Cm::$req->post('role_id',['null','empty']);
                    if(empty($roleID)){
                        Cm::$res->error("请选择角色！");
                    }
                    $mdata = [
                        'username' => Cm::$req->post('name', ['title' => '姓名']),
                        'account' => Cm::$req->post('account', ['title' => '登录账号']),
                        'pwd' => Cm::$req->post('pwd', ['title' => '登录密码']),
                        'status' => Cm::$req->post('status',['null','empty','default'=>1]),
                        'is_check' => Cm::$req->post('is_check',['null','empty','default'=>0]),
                        'remark' => Cm::$req->post('remark',['null','empty','default'=>'']),
                        'updatetime' => date('Y-m-d H:i:s')
                    ];
                    $account= Cm::$req->post('account');
                    $existMobile = Cm::$db->one("SELECT id FROM sys_manager WHERE account='{$mdata['account']}' AND id!={$id} AND status!=2 AND sys_id={$this->sysid}");
                    if (isset($existMobile['id'])){
                        Cm::$res->error("登录账号【{$account}】已经存在！");
                    }
                    $mdata['password'] = md5(md5($mdata['pwd']));
                    if ($id > 0) {
                        Cm::$db->update("sys_manager", $mdata, "id={$id}");
                        $ndata = ["role_id" => $roleID];
                        Cm::$db->update("sys_manager_role", $ndata,"manager_id={$id}");
                        Cm::$app->log->authlog(3,['sys_manager'=>$mdata,'sys_manager_role'=>$ndata],$id);
                    } else {
                        $mdata['addtime'] = date('Y-m-d H:i:s');
                        $mdata['sys_id'] = $this->sysid;
                        $id = Cm::$db->insert("sys_manager", $mdata);
                        $ndata = ["manager_id" => $id, "role_id" => $roleID];
                        Cm::$db->insert("sys_manager_role", $ndata);
                        Cm::$app->log->authlog(4,['sys_manager'=>$mdata,'sys_manager_role'=>$ndata],$id);
                    }

                    Cm::$res->susess([], '保存成功');
                    break;
                case "del":
                    $id = Cm::$req->post('id');
                    $mdata = ['status' => 2,'updatetime'=>date('Y-m-d H:i:s')];
                    Cm::$db->update("sys_manager", $mdata, "id={$id} and sys_id={$this->sysid}");
                    Cm::$app->log->authlog(5,$mdata,$id);
                    Cm::$res->susess();
                    break;
                case 'loglist':
                    $pageIndex = Cm::$req->post('page', ['null', 'empty', 'default' => 1]);
                    $pageCount = Cm::$req->post('limit', ['null', 'empty', 'default' => 20]);
                    $where = " 1=1 ";
                    $id = (int)Cm::$req->get('id', ['null', 'empty', 'default' => 0]);
                    if(empty($id)){
                        Cm::$res->error("选择管理员");
                    }else{
                        $where .= " AND manager_id={$id}";
                    }
                    $authlogtype = $this->authlogtype();
                    $data = Cm::$db->paging("SELECT * FROM auth_log WHERE {$where} ORDER BY addtime DESC",$pageIndex, $pageCount);
                    foreach ($data['data'] as $key => &$value) {
                        $value['typestr'] = $authlogtype[$value['type']];
                    }
                    Cm::$res->paging($data);    
                    break;    

            }
            Cm::$res->error();
        }

        $data['roles'] = Cm::$db->all("SELECT id,name FROM sys_auth_role WHERE sys_id={$this->sysid} AND status=1 ORDER BY sort");
        $data['map'] = [];
        $data['map'][] = ['title' => "权限管理", 'url' => Url::to(['sys/auth'])];
        $data['map'][] = ['title' => "管理员管理", 'url' => Url::to(['sys/manager'])];
        $data['baseUrl'] = Url::to(['sys/manager']);
        return $data;
    }

    /*
    * 系统配置
    */
    public function actionIndex(){
        $rule_type = [1=>'购票',2=>'余票',3=>'检票',4=>'订单完成',5=>'改签',6=>'全款退票',7=>'不能退票',8=>'退票手续费',9=>'分销'];
        if (Cm::$req->isPost()) {
            $action = Cm::$req->get('action');
            switch ($action) {
                case "list":
                    $pageIndex = Cm::$req->post('page', ['null', 'empty', 'default' => 1]);
                    $pageCount = Cm::$req->post('limit', ['null', 'empty', 'default' => 20]);
                    $status = (int)Cm::$req->post('is_status', ['null', 'empty']);
                    $where = " 1=1 ";
                    if(!empty($status)){
                        $status = $status == 2 ? 0 : $status;
                        $where .= " AND a.status={$status} ";
                    }else{
                        $where .= " AND a.status!=2 ";
                    }
                    $data = Cm::$db->paging("SELECT a.*,c.name AS circuitstr FROM rule_setting a LEFT JOIN circuit c ON a.circuit_id=c.id WHERE {$where} ORDER BY a.addtime DESC",
                        $pageIndex, $pageCount);
                    $ids = ($pageIndex-1)*$pageCount;
                    foreach ($data['data'] as $key => &$value) {
                        $ids++;
                        $value['ids'] = $ids;
                        $value['typestr'] = $rule_type[$value['type']];
                        $value['circuitstr'] = $value['circuit_id'] == 0 ? '通用' : $value['circuitstr'];
                    }
                    Cm::$res->paging($data);
                    break;
                case "save":
                    $type = Cm::$req->post('type',['null','empty']);
                    if(empty($type)){
                        Cm::$res->error("参数错误");
                    }
                    $time = date("Y-m-d H:i:s",time());
                    switch ($type) {
                         case 'sys':
                            $sys_telephone = Cm::$req->post('sys_telephone',['title'=>'客服电话']);
                            $sys_service_charge = Cm::$req->post('sys_service_charge',['title'=>'服务费']);
                            $sys_premium = Cm::$req->post('sys_premium',['title'=>'保险费']);
                            $sys_order_change = Cm::$req->post('sys_order_change',['title'=>'改签最大次数']);
                            $sys_car_change = Cm::$req->post('sys_car_change',['title'=>'转车最大次数']);
                            $sys_car_pooling = Cm::$req->post('sys_car_pooling',['title'=>'拼车最大次数']);
                            $sys_retreat_notice = Cm::$req->post('sys_retreat_notice',['null','empty', 'default' => '']);
                            $sys_buy_notice = Cm::$req->post('sys_buy_notice',['null','empty', 'default' => '']);
                            $sys_rebate_notice = Cm::$req->post('sys_rebate_notice',['null','empty', 'default' => '']);

                            $sys_rebate_status = Cm::$req->post('sys_rebate_status',['title'=>'分销开关','null','empty', 'default' => 0]);
                            $sys_rebate_type = Cm::$req->post('sys_rebate_type',['title'=>'分销方式','null','empty', 'default' => 1]);
                            $sys_rebate = Cm::$req->post('sys_rebate',['title'=>'分销金额','null','empty', 'default' => 1]);
                            if(empty($sys_retreat_notice)){
                                Cm::$res->error("请填写退票须知");           
                            }
                            if(empty($sys_buy_notice)){
                                Cm::$res->error("请填写购票须知");           
                            }
                             if(empty($sys_rebate_notice)){
                                 Cm::$res->error("请填写购票须知");
                             }
                            if(!empty($sys_rebate_status)){
                                if(empty($sys_rebate_type))Cm::$res->error("请选择分销方式");
                                if(empty($sys_rebate))Cm::$res->error("请设置分销金额/比列");
                            }
                            if($sys_rebate_type==self::REBATE_TYPE_RATIO&&($sys_rebate>100||$sys_rebate<=0))Cm::$res->error("分销比列设置错误,比例不应大于100,也不应小于0.01");
                            if($sys_rebate_type==self::REBATE_TYPE_PRICE&&$sys_rebate<=0)Cm::$res->error("分销金额设置错误,不应小于0.01");
                            $sys_retreat_notice = htmlspecialchars($sys_retreat_notice);
                            $sys_buy_notice = htmlspecialchars($sys_buy_notice);
                            $sys_rebate_notice = htmlspecialchars($sys_rebate_notice);
                            $this->saveConfigValue('sys_telephone',$sys_telephone,$time);
                            $this->saveConfigValue('sys_service_charge',$sys_service_charge,$time);
                            $this->saveConfigValue('sys_premium',$sys_premium,$time);
                            $this->saveConfigValue('sys_order_change',$sys_order_change,$time);
                            $this->saveConfigValue('sys_car_change',$sys_car_change,$time);
                            $this->saveConfigValue('sys_car_pooling',$sys_car_pooling,$time);
                            $this->saveConfigValue('sys_buy_notice',$sys_buy_notice,$time);
                            $this->saveConfigValue('sys_retreat_notice',$sys_retreat_notice,$time);
                            $this->saveConfigValue('sys_rebate_notice',$sys_rebate_notice,$time);
                            $this->saveConfigValue('sys_rebate_status',$sys_rebate_status,$time);
                            $this->saveConfigValue('sys_rebate_type',$sys_rebate_type,$time);
                            $this->saveConfigValue('sys_rebate',$sys_rebate,$time);
                            $sys = Cm::$db->all("SELECT * FROM config_setting WHERE `group`='sys' AND status=1");
                            $sysdata = [];
                            if(!empty($sys)){
                                foreach ($sys as $key => $value) {
                                    $sysdata[$value['key']]['value'] = str_replace(array("\r\n", "\r", "\n"), "", $value['value']);
                                }
                            }
                            Cm::$app->log->authlog(12,$sysdata,"sys");
                            Cm::$res->susess($sysdata);
                            break;
                        case 'rule':
                            $id = Cm::$req->post('id', ['null', 'empty','default'=>0]);
                            $mdata = [
                                'status' => Cm::$req->post('status',['title'=>'状态']),
                                'name' => Cm::$req->post('name',['title'=>'规则名称']),
                                'circuit_id' => Cm::$req->post('circuit',['null', 'empty','default'=>0]),
                                'type' => Cm::$req->post('rule_type',['title'=>'规则类型']),
                                'addtime' => $time,
                                'updatetime' => $time
                            ];
                            $rule_value = Cm::$req->post('rule_value',['title'=>'规则内容']);
                            $exit = Cm::$db->one("SELECT id FROM rule_setting WHERE  type={$mdata['type']} AND circuit_id={$mdata['circuit_id']} AND status!=2 ");
                            if(!empty($exit) && $exit['id']!=$id){
                                Cm::$res->error('该规则已存在');
                            }
                            $mdata['value'] = $rule_value;
                            if(empty($id)){
                                $id = Cm::$db->insert("rule_setting", $mdata);
                                if($id){
                                    Cm::$app->log->authlog(14,$mdata,$id);
                                    Cm::$res->susess();
                                }else{
                                    Cm::$res->error('保存失败');
                                }
                            }else{
                                if($id==11)$mdata['value']=1;
                                Cm::$db->update("rule_setting", $mdata, " id={$id}");
                                Cm::$app->log->authlog(13,$mdata,$id);
                                Cm::$res->susess();
                            }
                            break;    
                     } 
                    break;
                case "del":
                    $id = Cm::$req->post('id');
                    $mdata = ['status' => 2,'updatetime'=>date('Y-m-d H:i:s')];
                    Cm::$db->update("rule_setting", $mdata, "id={$id}");
                    Cm::$app->log->authlog(15,$mdata,$id);
                    Cm::$res->susess();
                    break;
                case 'startstop':
                    $id = Cm::$req->post('id');
                    $status = Cm::$req->post('status');
                    $status = $status == 1 ? 0 : 1;
                    $mdata = ['status' => $status,'updatetime'=>date('Y-m-d H:i:s')];
                    Cm::$db->update("rule_setting", $mdata, "id={$id}");
                    Cm::$app->log->authlog(16,$mdata,$id);
                    Cm::$res->susess();
                    break; 
                case 'upload':
                    (new CmDataModel())->upload('notice','img');   
                    break;  
                case 'bannerlist':
                    $pageIndex = Cm::$req->post('page', ['null', 'empty', 'default' => 1]);
                    $pageCount = Cm::$req->post('limit', ['null', 'empty', 'default' => 20]);
                    $where = " status!=2 ";
                    $data = Cm::$db->paging("SELECT * FROM banner WHERE {$where} ORDER BY sort ASC",
                        $pageIndex, $pageCount);
                    $ids = ($pageIndex-1)*$pageCount;
                    foreach ($data['data'] as $key => &$value) {
                        $ids++;
                        $value['ids'] = $ids;
                    }
                    Cm::$res->paging($data);
                    break;
                case 'bannerupload':
                    (new CmDataModel())->upload('banner','img');             
                    break;      
                case 'bannersave':
                    $path = Cm::$req->post('path', ['null', 'empty', 'default' => '']);
                    $sort = Cm::$req->post('sort', ['null', 'empty', 'default' => 0]);
                    $link_url = Cm::$req->post('link_url', ['null', 'empty', 'default' => '']);
                    if(empty($path)){
                        Cm::$res->error('请选择图片');
                    }
                    $time = time();
                    $timestr = date('Y-m-d H:i:s',$time);
                    $mdata = [
                        'path'=>$path,
                        'sort'=>$sort,
                        'link_url'=>$link_url,
                        'status'=>1,
                        'addtime'=>$timestr,
                        'updatetime'=>$timestr,
                    ];
                    $id = Cm::$db->insert("banner", $mdata);
                    if($id){
                        Cm::$app->log->authlog(46,$mdata,$id);
                        Cm::$res->susess();
                    }else{
                        Cm::$res->error('保存失败');    
                    }
                    break; 
                case 'bannerdel':
                    $id = Cm::$req->post('id');
                    $mdata = ['status' => 2,'updatetime'=>date('Y-m-d H:i:s')];
                    Cm::$db->update("banner", $mdata, "id={$id}");
                    Cm::$app->log->authlog(47,$mdata,$id);
                    Cm::$res->susess();
                    break; 
                case 'bannerstartstop':
                    $id = Cm::$req->post('id');
                    $status = Cm::$req->post('status');
                    $status = $status == 1 ? 0 : 1;
                    $mdata = ['status' => $status,'updatetime'=>date('Y-m-d H:i:s')];
                    Cm::$db->update("banner", $mdata, "id={$id}");
                    Cm::$app->log->authlog(48,$mdata,$id);
                    Cm::$res->susess();
                    break;               
            }
        }

        $data['map'] = [];
        $data['map'][] = ['title' => "系统配置", 'url' => Url::to(['sys/index'])];
        $data['baseUrl'] = Url::to(['sys/index']);
        $data['imgurl'] = BASE_URL;
        $confilist = Cm::$db->all("SELECT * FROM config_setting WHERE status=1");
        $config = ['sys'=>[]];
        if(!empty($confilist)){
            foreach ($confilist as $key => $value) {
                if($value['key'] == 'sys_buy_notice' || $value['key'] == 'sys_retreat_notice' || $value['key'] == 'sys_rebate_notice'){
                    $value['value'] = str_replace(array("\r\n", "\r", "\n"), "", $value['value']);  
                }
                $config[$value['group']][$value['key']] = $value;
            }
        }
        $data['config'] = $config;
        $data['circuitlist'] = Cm::$db->all("SELECT id,name FROM circuit WHERE status=1 ORDER BY is_hot DESC");
        $data['rule_type'] = $rule_type;
        return $data;
    }

    /*
    *保存系统配置规则数据
    */
    private function saveConfigValue($key,$value,$time){
        $ishas = Cm::$db->one("SELECT id FROM config_setting WHERE `group`='sys' AND `key`='{$key}' AND status=1");
        if(empty($ishas)){
            Cm::$db->insert("config_setting", ["group" => 'sys', "key" => $key,'value'=>$value,'status' => 1,'addtime' => $time,'updatetime'=>$time]);
        }else{
            Cm::$db->update("config_setting", ['value'=>$value,'updatetime'=>$time],"id={$ishas['id']}");
        }
    }

    /*
    * 获取角色的权限
    */
    private function getRolePermissions($sysID, $roleID)
    {
        $apps = Cm::$db->all("SELECT a.id+100000 AS id,0 AS pId,a.name,IFNULL(b.num_all,0) AS num_all,IFNULL(c.num_role,0) AS num_role 
            FROM sys_auth_modules a
            INNER JOIN (
                SELECT module_id,count(*) AS num_all 
                FROM sys_auth_module_controller_action_permissions 
                WHERE `status`=1 AND deprecated=0 
                GROUP BY module_id
            ) b on a.id=b.module_id
            LEFT JOIN (
                SELECT module_id,count(*) AS num_role 
                FROM sys_auth_module_controller_action_permissions 
                WHERE id IN (
                    SELECT permissions_id 
                    FROM sys_auth_role_permissions 
                    WHERE role_id={$roleID}
                ) 
                GROUP BY module_id
            ) c on a.id=c.module_id 
            WHERE a.`status`=1 AND a.deprecated=0  AND a.sys_id={$sysID} 
            ORDER BY a.sort ASC");

        $modules = Cm::$db->all("SELECT a.id+200000 AS id,a.module_id+100000 AS pId,a.name,IFNULL(b.num_all,0) AS num_all,IFNULL(c.num_role,0) AS num_role 
            FROM sys_auth_module_controllers a
            INNER JOIN (
                SELECT controller_id,count(*) AS num_all 
                FROM sys_auth_module_controller_action_permissions 
                WHERE `status`=1 AND deprecated=0 
                GROUP BY controller_id
            ) b on a.id=b.controller_id
            LEFT JOIN (
                SELECT controller_id,count(*) AS num_role 
                FROM sys_auth_module_controller_action_permissions 
                WHERE id IN (
                    SELECT permissions_id 
                    FROM sys_auth_role_permissions 
                    WHERE role_id={$roleID}
                ) 
                GROUP BY controller_id
            ) c on a.id=c.controller_id 
            WHERE a.`status`=1 AND a.deprecated=0  AND a.sys_id={$sysID} 
            ORDER BY a.sort ASC");

        $actions = Cm::$db->all("SELECT a.id+300000 AS id,a.controller_id+200000 AS pId,a.name,IFNULL(b.num_all,0) AS num_all,IFNULL(c.num_role,0) AS num_role 
            FROM sys_auth_module_controller_actions a
            INNER JOIN (
                SELECT action_id,count(*) AS num_all 
                FROM sys_auth_module_controller_action_permissions 
                WHERE `status`=1 AND deprecated=0 AND module_id>0 
                GROUP BY action_id
            ) b on a.id=b.action_id
            LEFT JOIN (
                SELECT action_id,count(*) AS num_role 
                FROM sys_auth_module_controller_action_permissions 
                WHERE id IN (
                    SELECT permissions_id 
                    FROM sys_auth_role_permissions 
                    WHERE role_id={$roleID}
                ) 
                GROUP BY action_id
            ) c on a.id=c.action_id
            WHERE a.`status`=1 AND a.deprecated=0  AND a.sys_id={$sysID} 
            ORDER BY a.sort ASC");

        $permissions = Cm::$db->all("SELECT a.id,a.action_id+300000 AS pId,a.name,IFNULL(b.id,0) AS checked 
            FROM sys_auth_module_controller_action_permissions a
            LEFT JOIN (
                SELECT * FROM sys_auth_role_permissions 
                WHERE role_id={$roleID}
            ) b on a.id=b.permissions_id
            WHERE a.sys_id={$sysID} AND a.status=1 AND a.deprecated=0 
            ORDER BY id ASC");

        foreach ($apps as &$app) {
            if ($app['num_role']) {
                $app['checked'] = true;
            }
            unset($app['num_all']);
            unset($app['num_role']);
        }

        foreach ($modules as &$mod) {
            if ($mod['num_role']) {
                $mod['checked'] = true;
            }
            unset($mod['num_all']);
            unset($mod['num_role']);
        }

        foreach ($actions as &$ac) {
            if ($ac['num_role']) {
                $ac['checked'] = true;
            }
            unset($ac['num_all']);
            unset($ac['num_role']);
        }

        foreach ($permissions as &$m) {
            if ($m['checked'] > 0)
                $m['checked'] = true;
            else
                unset($m['checked']);
        }

        return array_merge($apps, $modules, $actions, $permissions);
    }
}